﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web;

namespace TennisSite.Tools
{
    public class ExcelHelper
    {
        private const string EXCEL_2003_EXTENSION = "xls";
        private const string EXCEL_2007_EXTENSION = "xlsx";
        private const string EXCEL_2003_CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= {0};Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;\"";
        private const string EXCEL_2007_CONNECTION_STRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= {0};Mode=ReadWrite;Extended Properties=\"Excel 12.0;HDR=YES;\"";
        private const string DefaultSheetName = "Sheet1$";
        private const string SELECT_SHEET_QUERY = "SELECT * FROM [{0}]";
        private const string CREATE_SHEET_QUERY = "CREATE TABLE [{0}] ([FullName] string, [Gender] string, [User] string, [Country] string)";
        private const string INSERT_QUERY = "INSERT INTO [{0}] ([FullName], [Gender], [User], [Country]) VALUES ('{1}', '{2}', '{3}', '{4}')";


        public static void ExportToExcel(System.Data.DataTable dataTable, byte[] templateBuffer)
        {
            string filePath = Path.Combine(Path.GetTempPath(), Path.GetTempFileName());

            CreateExcelWorkbook(filePath, templateBuffer);

            if (File.Exists(filePath))
            {
                InsertData(dataTable, filePath);
            }

            DownloadFile(filePath);
        }

        private static void DownloadFile(string filePath)
        {
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();

            string filename = string.Format("PlayersList_{0}.xls", DateTime.Now.ToString("yyyyMMdd_hhmmss"));
            response.AppendHeader("content-disposition", "attachment; filename=" + filename);
            response.ContentType = "Application/vnd.ms-excel";
            response.WriteFile(filePath);
            response.Clear();
        }

        private static void CreateExcelWorkbook(string filePath, byte[] templateBuffer)
        {
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }

            File.WriteAllBytes(filePath, templateBuffer);

            string connString = string.Format(EXCEL_2003_CONNECTION_STRING, filePath);

            using (OleDbConnection connection = new OleDbConnection(connString))
            {
                string query = string.Format(CREATE_SHEET_QUERY, DefaultSheetName);

                using (OleDbCommand command = new OleDbCommand(query, connection))
                {
                    connection.Open();

                    command.ExecuteNonQuery();
                }
            }
        }

        private static void InsertData(System.Data.DataTable table, string filePath)
        {
            if (!string.IsNullOrEmpty(filePath) && table.Rows.Count > 0)
            {
                string connString = string.Format(EXCEL_2003_CONNECTION_STRING, filePath);

                using (OleDbConnection connection = new OleDbConnection(connString))
                {
                    connection.Open();
                    foreach (DataRow row in table.Rows)
                    {
                        string query = string.Format(INSERT_QUERY, DefaultSheetName, row["FullName"], row["Gender"], row["User"], row["Country"]);

                        using (OleDbCommand command = new OleDbCommand(query, connection))
                        {
                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        private static void ReleaseComObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }

        public static List<System.Data.DataTable> GetValidTables(string filePath, string[] requiredColumns)
        {
            List<System.Data.DataTable> tables = new List<System.Data.DataTable>();

            string[] sheetNames = GetSheets(filePath);

            foreach (string sheetName in sheetNames)
            {
                foreach (System.Data.DataTable table in ParseAndGetDataSet(filePath, sheetName).Tables)
                {
                    if (ValidateColumns(table, requiredColumns))
                    {
                        tables.Add(table);
                    }
                }
            }

            return tables;
        }

        private static DataSet ParseAndGetDataSet(string filePath, string sheetName)
        {
            DataSet result;
            OleDbConnection excelConnection = null;
            try
            {
                string excelExten;
                if (filePath.EndsWith(EXCEL_2003_EXTENSION, StringComparison.InvariantCultureIgnoreCase))
                {
                    excelExten = EXCEL_2003_EXTENSION;
                    excelConnection = new OleDbConnection(GetExcelConnectionString(filePath, excelExten));
                }
                else if (filePath.EndsWith(EXCEL_2007_EXTENSION, StringComparison.InvariantCultureIgnoreCase))
                {
                    excelExten = EXCEL_2007_EXTENSION;
                    excelConnection = new OleDbConnection(GetExcelConnectionString(filePath, excelExten));
                }
                result = GetExcelData(excelConnection, sheetName);
            }
            finally
            {
                if (excelConnection != null) excelConnection.Close();
            }
            return result;
        }

        private static DataSet ParseAndGetDataSet(string filePath, int sheetIndex)
        {
            DataSet result;
            OleDbConnection xlConn = null;
            try
            {
                string excelExten;
                if (filePath.EndsWith(EXCEL_2003_EXTENSION, StringComparison.InvariantCultureIgnoreCase))
                {
                    excelExten = EXCEL_2003_EXTENSION;
                    xlConn = new OleDbConnection(GetExcelConnectionString(filePath, excelExten));
                }
                else if (filePath.EndsWith(EXCEL_2007_EXTENSION, StringComparison.InvariantCultureIgnoreCase))
                {
                    excelExten = EXCEL_2007_EXTENSION;
                    xlConn = new OleDbConnection(GetExcelConnectionString(filePath, excelExten));
                }
                var sheets = GetExcelSheetNames(xlConn);
                result = GetExcelData(xlConn, sheets[sheetIndex]);
            }
            finally
            {
                if (xlConn != null) xlConn.Close();
            }
            return result;
        }

        private static string[] GetSheets(string filePath)
        {
            OleDbConnection excelConnection = null;
            string[] sheets;

            try
            {
                string extension;

                if (filePath.EndsWith(EXCEL_2003_EXTENSION, StringComparison.InvariantCultureIgnoreCase))
                {
                    extension = EXCEL_2003_EXTENSION;
                    excelConnection = new OleDbConnection(GetExcelConnectionString(filePath, extension));
                }
                else if (filePath.EndsWith(EXCEL_2007_EXTENSION, StringComparison.InvariantCultureIgnoreCase))
                {
                    extension = EXCEL_2007_EXTENSION;
                    excelConnection = new OleDbConnection(GetExcelConnectionString(filePath, extension));
                }
                sheets = GetExcelSheetNames(excelConnection);
            }
            finally
            {
                if (excelConnection != null) excelConnection.Close();
            }
            return sheets;
        }

        private static string GetExcelConnectionString(string filePath, string extension)
        {
            string connString = string.Empty;

            if (extension == EXCEL_2003_EXTENSION)
            {

                connString = string.Format(EXCEL_2003_CONNECTION_STRING, filePath);
            }
            else if (extension == EXCEL_2007_EXTENSION)
            {

                connString = string.Format(EXCEL_2007_CONNECTION_STRING, filePath);
            }
            return connString;
        }

        private static DataSet GetExcelData(OleDbConnection excelConnection, string sheetName)
        {
            string queryString = string.Format(SELECT_SHEET_QUERY, sheetName);
            OleDbCommand command = new OleDbCommand(queryString, excelConnection);
            command.Connection = excelConnection;
            DataSet dataset = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            try
            {
                excelConnection.Open();

                command.CommandType = CommandType.Text;
                adapter.SelectCommand = command;
                adapter.Fill(dataset);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                excelConnection.Close();
            }
            return dataset;

        }

        private static string[] GetExcelSheetNames(OleDbConnection excelConnection)
        {
            excelConnection.Open();
            System.Data.DataTable dataTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string[] excelSheets = new string[dataTable.Rows.Count];

            int i = 0;

            // Add the sheet name to the string array.
            foreach (DataRow row in dataTable.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }
            excelConnection.Close();

            return excelSheets;
        }

        private static bool ValidateColumns(System.Data.DataTable table, string[] requiredColumns)
        {
            foreach (string column in requiredColumns)
            {
                if (!table.Columns.Contains(column))
                {
                    return false;
                }
            }

            return true;
        }
    }
}